/**
 * @author Laboratio Av I
 * @since 18/04/2012 14:03:56
 */
package ar.edu.utn.frgp.banco.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import ar.edu.utn.frgp.banco.dao.ClienteDao;
import ar.edu.utn.frgp.banco.dao.connection.ConnectionHandler;
import ar.edu.utn.frgp.banco.dao.connection.IConnectionHandler;
import ar.edu.utn.frgp.banco.domain.Cliente;
import ar.edu.utn.frgp.banco.exceptions.DataAccessException;


public class ClienteDaoImpl implements ClienteDao {

	

	public void altaCliente(Cliente cliente) throws DataAccessException {

		PreparedStatement ps = null;
		
		IConnectionHandler handler = new ConnectionHandler();
		Connection cn = handler.getConnection();
		
		try {
			ps = cn.prepareStatement("INSERT INTO clientes VALUES(?,?,?,?,?,?)");
			
			ps.setInt   (1, cliente.getDni());
			ps.setString(2, cliente.getNombre());
			ps.setString(3, cliente.getApellido());
			ps.setString(4, cliente.getDireccion());
			ps.setString(5, cliente.getEmail());
			ps.setInt   (6, cliente.getEstado());
			
			ps.executeUpdate();
			
		} catch (SQLException e) {
			throw new DataAccessException(e);
		} finally {
			handler.closeResources(ps, cn);
		}

	}

	
	public Cliente obtenerCliente(int dni) throws DataAccessException {
		
		 Cliente cliente = null;
	     Statement st = null;
         ResultSet rs = null;
		
		IConnectionHandler handler = new ConnectionHandler();
		Connection cn = handler.getConnection();
		
		try {
			st = cn.createStatement();
			rs = st.executeQuery("SELECT * FROM clientes where clientes.dni = " + dni);
			
			while (rs.next()) {
				
				cliente = new Cliente();
				
				cliente.setDni(dni);
				cliente.setNombre(rs.getString("nombre"));
				cliente.setApellido(rs.getString("apellido"));
				cliente.setDireccion(rs.getString("direccion"));
				cliente.setEmail(rs.getString("email"));
				cliente.setEstado(rs.getInt("estado"));
			}
		} catch (SQLException e) {
			System.out.println("Error en algo");
			e.printStackTrace();			
		} finally {
			handler.closeResources(st, cn);
		}
		return cliente;
	}

	@Override
	public List<Cliente> traerTodo() throws DataAccessException {
		 Statement st = null;
	      ResultSet rs = null;
			
			IConnectionHandler handler = new ConnectionHandler();
			Connection cn = handler.getConnection();
			
			List<Cliente> clientes = new ArrayList<Cliente>();
			
			try {
				st = cn.createStatement();
				rs = st.executeQuery("SELECT * FROM clientes");
				
				Cliente cl = null;
				
				while (rs.next()) {
					
					cl = new Cliente();
					cl.setDni(rs.getInt("dni"));
					cl.setNombre(rs.getString("nombre"));
					cl.setApellido(rs.getString("apellido"));
					cl.setDireccion(rs.getString("direccion"));
					cl.setEmail(rs.getString("email"));
					
					clientes.add(cl);
				}
						
							
			} catch (SQLException e) {
				System.out.println("Error al crear tablas.");
				e.printStackTrace();			
			} finally {
				handler.closeResources(st, cn);
			}
		return clientes;
	}

	@Override
	public List<Cliente> traerActivos() throws DataAccessException {
		 Statement st = null;
	      ResultSet rs = null;
			
			IConnectionHandler handler = new ConnectionHandler();
			Connection cn = handler.getConnection();
			
			List<Cliente> clientes = new ArrayList<Cliente>();
			
			try {
				st = cn.createStatement();
				rs = st.executeQuery("SELECT * FROM clientes");
				
				Cliente cl = null;
				
				while (rs.next()) {
					
					if (rs.getInt("estado")==1)
					{
						cl = new Cliente();
						cl.setDni(rs.getInt("dni"));
						cl.setNombre(rs.getString("nombre"));
						cl.setApellido(rs.getString("apellido"));
						cl.setDireccion(rs.getString("direccion"));
						cl.setEmail(rs.getString("email"));
						
						clientes.add(cl);
					}
				}
			} catch (SQLException e) {
				System.out.println("Error al consultar tablas.");
				e.printStackTrace();			
			} finally {
				handler.closeResources(st, cn);
			}
			
			return clientes;
	}


	public void bajarCliente(Cliente cl) throws DataAccessException {
		
	
	     Statement st = null;
 
		
		IConnectionHandler handler = new ConnectionHandler();
		Connection cn = handler.getConnection();
		
			try {
				st = cn.createStatement();
				st.executeUpdate("UPDATE clientes SET estado = 0 where clientes.dni = " + cl.getDni());
				System.out.println("El cliente se dio de baja con exito" );
			} catch (SQLException e) {
				System.out.println("Error al modificar tablas.");
				e.printStackTrace();
			}
			finally {
				handler.closeResources(st, cn);
			 		}
	}

	@Override
	public void levantarCliente(Cliente cl) throws DataAccessException {
		  
			Statement st = null;
		  			
			IConnectionHandler handler = new ConnectionHandler();
			Connection cn = handler.getConnection();
			
				try {
					st = cn.createStatement();
					st.executeUpdate("UPDATE clientes SET estado = 1 where clientes.dni = " + cl.getDni());
					System.out.println("El cliente se levanto con exito" );
				} catch (SQLException e) {
					System.out.println("Error al modificar tablas.");
					e.printStackTrace();
				}
				finally {
					handler.closeResources(st, cn);
				 		}
		
	}

	@Override
	public void actualizarCliente(Cliente cl) throws DataAccessException {
		  
		    PreparedStatement ps = null;
		    Cliente cl2 = new Cliente();
		  			
			IConnectionHandler handler = new ConnectionHandler();
			Connection cn = handler.getConnection();
			
				try {
					
					ps = cn.prepareStatement("UPDATE clientes  SET nombre = ?" + "where dni = ?");
					cl2.cargarCliente();
					ps.setString(1, cl2.getNombre());
					ps.setInt   (2, cl.getDni());
					ps.executeUpdate();
					
					ps = cn.prepareStatement("UPDATE clientes  SET apellido = ?" + "where dni = ?");
					ps.setString(1, cl2.getApellido());
					ps.setInt   (2, cl.getDni());
					ps.executeUpdate();
					
					ps = cn.prepareStatement("UPDATE clientes  SET direccion = ?" + "where dni = ?");
					ps.setString(1, cl2.getDireccion());
					ps.setInt   (2, cl.getDni());
					ps.executeUpdate();
					
					ps = cn.prepareStatement("UPDATE clientes  SET email = ?" + "where dni = ?");
					ps.setString(1, cl2.getEmail());
					ps.setInt   (2, cl.getDni());
					ps.executeUpdate();
									
					System.out.println("El cliente se modifico con exito" );
				} catch (SQLException e) {
					System.out.println("Error al modificar tablas.");
					throw new DataAccessException(e);
				}
				 finally {
					handler.closeResources(ps, cn);
				 		}
	}

}
